package com.cn.lyl.dao.impl;

import com.cn.lyl.dao.LawyerDao;
import com.cn.lyl.model.LawyerModel;
import com.cn.lyl.util.JDBC;
import com.cn.lyl.util.Tool;

import java.sql.*;
import java.util.ArrayList;
import java.util.List;

public class LawyerDaoImpl implements LawyerDao {
    @Override
    public int add(LawyerModel lawyerModel) {

        String sql ="INSERT INTO lawyer (lawyer_name, lawyer_intro, head, create_time, update_time, enable, remark) VALUES"+
                "('"+ Tool.stringIsNotNull(lawyerModel.getLawyerName())+"','"+Tool.stringIsNotNull(lawyerModel.getLawyerIntro())+"','"+
                Tool.stringIsNotNull(lawyerModel.getHead())+"',now(),now(),1,'')";
        System.out.println("插入语句："+sql);

        return JDBC.update(sql);
        //return JDBC.insert(sql_l,sql_l_id,sql_r,"lawyer_role_dependence","lawyer_id","role_id");

    }

    @Override
    public int update(LawyerModel lawyerModel) {
        String sql="update   lawyer l  left join lawyer_role_dependence d on l.id = d.lawyer_id set ";
        StringBuffer stringBuffer = new StringBuffer();
        if(!"".equals(lawyerModel.getLawyerName())&&lawyerModel!=null) {
            if (stringBuffer.length() > 0) {
                stringBuffer.append(",");
            }
            stringBuffer.append(" l.lawyer_name = '"+lawyerModel.getLawyerName()+"'");
        }
        if (!"".equals(lawyerModel.getLawyerIntro())&&lawyerModel.getLawyerIntro()!=null)
        {
            if (stringBuffer.length() > 0) {
                stringBuffer.append(",");
            }
            stringBuffer.append(" l.lawyer_intro = '"+lawyerModel.getLawyerIntro()+"'");
        }
        if(!"".equals(lawyerModel.getHead())&&lawyerModel!=null){
            if (stringBuffer.length() > 0) {
                stringBuffer.append(",");
            }
            stringBuffer.append(" l.head = '"+lawyerModel.getHead()+"'");
        }
        if(!"".equals(lawyerModel.getDependenceObj())&&lawyerModel.getDependenceObj()!=null){
            if (stringBuffer.length() > 0) {
                stringBuffer.append(",");
            }
            stringBuffer.append("d.role_id = (select id from lawyer_role where role = '"+lawyerModel.getDependenceObj()+"')");
        }
        if(stringBuffer.length()>0){
            stringBuffer.append(", l.update_time = now()");
        }
        if(stringBuffer.length()>0){
            stringBuffer.append(",");
        }
        stringBuffer.append(" l.remark='' where l.id="+lawyerModel.getId());
        sql=sql+stringBuffer.toString();
        System.out.println("修改："+sql);
        return JDBC.update(sql);
    }

    @Override
    public int delete(LawyerModel lawyerModel) {
        String sqlDelete="delete from lawyer where id ="+lawyerModel.getId();

        return JDBC.update(sqlDelete);

    }

    @Override
    public LawyerModel findById(int id) {
        String sql ="select l.id , l.lawyer_name ,l.is_home,l.head,l.lawyer_intro, r.role from lawyer l  left join lawyer_role_dependence d on l.id = d.lawyer_id left join lawyer_role r on r.id = d.role_id where l.id= "+id;
        return setList(sql).size()>0?setList(sql).get(0):null;

    }

    @Override
    public LawyerModel findNewest() {
        String sql ="SELECT id from lawyer order by id desc ";
        LawyerModel lawyerModel = new LawyerModel();
        ResultSet query = JDBC.query(sql);
        try{
            if(query.next()){
                lawyerModel.setId(query.getInt("id"));
            }
        }catch (Exception e){

        }
        return lawyerModel;
    }

    @Override
    public List<LawyerModel> findAll(LawyerModel lawyerModel) {
        String sql ="select l.id , l.lawyer_name ,l.is_home,l.head,l.lawyer_intro, r.role from lawyer l  left join lawyer_role_dependence d on l.id = d.lawyer_id left join lawyer_role r on r.id = d.role_id where 1=1 ";
        String stringSql = commonSql(lawyerModel);
        int pageCurr=lawyerModel.getPage().getPageCurr();
        int pageSize=lawyerModel.getPage().getPageSize();
        stringSql +=" order by l.id desc limit "+(pageCurr-1)*pageSize+","+pageSize;
        sql = sql + stringSql;

        return setList(sql);
    }

    @Override
    public int countFindAll(LawyerModel lawyerModel) {
        String sql ="select count(l.id) count from lawyer l  left join lawyer_role_dependence d on l.id = d.lawyer_id left join lawyer_role r on r.id = d.role_id where 1=1 ";
        String stringSql = commonSql(lawyerModel);
        sql= sql+stringSql;


        ResultSet query = JDBC.query(sql);
        int size = 0;
        try {
            while (query.next()) {
                size = query.getInt("count");
            }
        }catch (SQLException e) {
            e.printStackTrace();
        }
        return size;

    }

    @Override
    public int updateIsHome(LawyerModel lawyerModel) {
        String sql = "update lawyer set is_home = ABS(1-is_home) where id=" + lawyerModel.getId();
        return JDBC.update(sql);
    }


    private List<LawyerModel> setList(String sql) {
        ResultSet resultSet = JDBC.query(sql);
        List<String> colList = new ArrayList<>();
        try {
            ResultSetMetaData metaData = resultSet.getMetaData();
            for (int i = 1; i <=metaData.getColumnCount(); i++) {

                colList.add(metaData.getColumnLabel(i));

            }
        } catch (SQLException e) {
            return null;
        }
        List<LawyerModel> list = new ArrayList<>();
        try {
            while (resultSet.next()) {
                LawyerModel model = new LawyerModel();
                if (colList.contains("id")) {
                    model.setId(resultSet.getInt("id"));
                }
                if (colList.contains("is_home")) {
                    int num=resultSet.getInt("is_home");
                    if(num>0){
                        model.setIsHome("是");
                    }
                    else {
                        model.setIsHome("否");
                    }
                }
                if (colList.contains("lawyer_name")) {
                    model.setLawyerName(resultSet.getString("lawyer_name"));
                }
                if (colList.contains("create_time")) {
                    model.setCreateTime(resultSet.getString("create_time"));
                }
                if (colList.contains("update_time")) {
                    model.setUpdateTime(resultSet.getString("update_time"));
                }
                if (colList.contains("enable")) {
                    model.setEnable(resultSet.getInt("enable"));
                }
                if (colList.contains("lawyer_intro")) {
                    model.setLawyerIntro(resultSet.getString("lawyer_intro"));
                }
                if (colList.contains("remark")) {
                    model.setRemark(resultSet.getString("remark"));
                }
                if (colList.contains("head")) {
                    model.setHead(resultSet.getString("head"));
                }
                if(colList.contains("role")){
                    model.setDependenceObj(resultSet.getString("role"));
                }

                list.add(model);
            }
        } catch (SQLException e) {
            e.printStackTrace();
        }
       /* if (list.size() == 0) {
            list.add(null);
        }*/
        return list;
    }

    private String commonSql(LawyerModel lawyerModel) {
        StringBuffer stringBuffer = new StringBuffer();
        if (lawyerModel.getLawyerName() != null && !"".equals(lawyerModel.getLawyerName())) {
            stringBuffer.append(" and l.lawyer_name like'%").append(lawyerModel.getLawyerName()).append("%'");
        }
        if (lawyerModel.getLawyerIntro() != null && !"".equals(lawyerModel.getLawyerIntro())) {
            stringBuffer.append(" and l.lawyer_intro like '%").append(lawyerModel.getLawyerIntro()).append("%'");
        }
        if (lawyerModel.getIsHome()!=null&&!"".equals(lawyerModel.getIsHome())&&!"全部".equals(lawyerModel.getIsHome())) {
            if(lawyerModel.getIsHome().equals("是")){
                stringBuffer.append(" and l.is_home =").append(1);
            }else{
                stringBuffer.append(" and l.is_home =").append(0);
            }

        }
        if (lawyerModel.getDependenceObj() != null && !"".equals(lawyerModel.getDependenceObj())&&!"全部".equals(lawyerModel.getDependenceObj())) {
            stringBuffer.append(" and r.role='"+(String)lawyerModel.getDependenceObj()+"'");
        }


        return stringBuffer.toString();
    }
}
